package com.xmy.cultivate.mapper;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.xmy.cultivate.entity.Course;
import com.xmy.cultivate.entity.Recruit;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xmy.cultivate.entity.RecruitList;
import com.xmy.cultivate.entity.SchoolNeeds;
import com.xmy.cultivate.respons.RecruitAnalysisRs;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.time.LocalDateTime;
import java.util.List;

/**
 * <p>
 * 招聘信息 Mapper 接口
 * </p>
 *
 * @author hpiggy
 * @since 2024-11-27
 */
public interface RecruitMapper extends BaseMapper<Recruit> {
    @Select("SELECT * FROM recruit ${ew.customSqlSegment}")
    public abstract IPage<Recruit> findAll(IPage<?> page, @Param(Constants.WRAPPER) Wrapper wrapper);


    @Select({"<script>",
            "SELECT ",
            "r.id, ",
            "r.inviter_id, ",
            "s.name AS teacherName, ",
            "COUNT(CASE WHEN r.created_at BETWEEN #{startDate} AND #{endDate} THEN 1 END) AS recruitCount, ",
            "COUNT(CASE WHEN r.invite_date_time BETWEEN #{startDate} AND #{endDate} AND r.pass_type != 0 THEN 1 END) AS recruitInterviewCount, ",
            "COUNT(CASE WHEN r.is_pass_time BETWEEN #{startDate} AND #{endDate} AND r.pass_type = 1 THEN 1 END) AS recruitPassCount, ",
            "COUNT(CASE WHEN r.is_pass_time BETWEEN #{startDate} AND #{endDate} AND r.pass_type = 2 THEN 1 END) AS recruitNoPassCount, ",
            "COUNT(CASE WHEN r.give_up_time BETWEEN #{startDate} AND #{endDate} AND r.is_give_up = 1 THEN 1 END) AS tryJobGiveUpCount, ",
            "COALESCE(SUM(tj.tryJobCount), 0) AS tryJobCount,",
            "COALESCE(SUM(tj.tryJobPassCount), 0) AS tryJobPassCount,",
            "COALESCE(SUM(tj.tryJobNoPassCount), 0) AS tryJobNoPassCount",
            "FROM adms_recruit r ",
            "LEFT JOIN staff s ON s.id = r.inviter_id ",
            "AND s.deleted = 0",
            "LEFT JOIN (",
            "SELECT ",
            "tj.recruit_id,",
            "COUNT(CASE WHEN tj.try_date BETWEEN #{startDate} AND #{endDate} THEN 1 END) AS tryJobCount,",
            "COUNT(CASE WHEN tj.try_audit_date BETWEEN #{startDate} AND #{endDate} AND tj.pass_type = 1 THEN 1 END) AS tryJobPassCount,",
            "COUNT(CASE WHEN tj.try_audit_date BETWEEN #{startDate} AND #{endDate} AND tj.pass_type = 2 THEN 1 END) AS tryJobNoPassCount",
            "FROM adms_try_job tj",
            "GROUP BY tj.recruit_id",
            ") tj ON tj.recruit_id = r.id",
            "WHERE",
            "1 = 1",
            "GROUP BY r.inviter_id",
            "</script>"})
    public List<RecruitList> countList(@Param("startDate") String startDate, @Param("endDate") String endDate);


    @Select({"<script>",
            "SELECT ",
            "subquery.inviter_id,",
            "subquery.teacher_name,",
            "subquery.recruit_date AS max_date,",
            "subquery.daily_recruit_count AS max_count",
            "FROM (",
            "SELECT ",
            "r.inviter_id,",
            "s.name AS teacher_name,",
            "<if test='recruitStatus!=null and recruitStatus==0'>",
            "DATE(r.created_at) AS recruit_date,",
            "</if>",
            "<if test='recruitStatus!=null and recruitStatus==1'>",
            "DATE(r.invite_date_time) AS recruit_date,",
            "</if>",
            "<if test='recruitStatus!=null and (recruitStatus==2 or recruitStatus==3)'>",
            "DATE(r.is_pass_time) AS recruit_date,",
            "</if>",
            "<if test='recruitStatus!=null and recruitStatus==7'>",
            "DATE(r.give_up_time) AS recruit_date,",
            "</if>",
            "<if test='recruitStatus!=null and recruitStatus==0'>",
            "COUNT(r.id) AS daily_recruit_count,",
            "RANK() OVER (PARTITION BY r.inviter_id ORDER BY COUNT(r.id) DESC,DATE(r.created_at)) AS ranking",
            "</if>",
            "<if test='recruitStatus!=null and recruitStatus==1'>",
            "COUNT(r.id) AS daily_recruit_count,",
            "RANK() OVER (PARTITION BY r.inviter_id ORDER BY COUNT(r.id) DESC,DATE(r.invite_date_time)) AS ranking",
            "</if>",
            "<if test='recruitStatus!=null and recruitStatus==2'>",
            "SUM(CASE WHEN r.pass_type = 1 THEN 1 ELSE 0 END) AS daily_recruit_count,",
            " RANK() OVER (PARTITION BY r.inviter_id ORDER BY SUM(CASE WHEN r.pass_type = 1 THEN 1 ELSE 0 END) DESC,DATE(r.is_pass_time)) AS ranking",
            "</if>",
            "<if test='recruitStatus!=null and recruitStatus==3'>",
            "SUM(CASE WHEN r.pass_type = 2 THEN 1 ELSE 0 END) AS daily_recruit_count,",
            " RANK() OVER (PARTITION BY r.inviter_id ORDER BY SUM(CASE WHEN r.pass_type = 2 THEN 1 ELSE 0 END) DESC,DATE(r.is_pass_time)) AS ranking",
            "</if>",
            "<if test='recruitStatus!=null and recruitStatus==7'>",
            "COUNT(r.id) AS daily_recruit_count,",
            "RANK() OVER (PARTITION BY r.inviter_id ORDER BY COUNT(r.id) DESC,DATE(r.give_up_time)) AS ranking",
            "</if>",
            "FROM ",
            "adms_recruit r",
            "LEFT JOIN staff s ON s.id = r.inviter_id",
            "WHERE",
            "<if test='recruitStatus!=null and recruitStatus==0'>",
            "r.created_at is not null",
            "</if>",
            "<if test='recruitStatus!=null and recruitStatus==1'>",
            "r.invite_date_time is not null",
            "</if>",
            "<if test='recruitStatus!=null and (recruitStatus==2 or recruitStatus==3)'>",
            "r.is_pass_time IS NOT NULL",
            "</if>",
            "<if test='recruitStatus!=null and recruitStatus==7'>",
            "r.give_up_time IS NOT NULL",
            "</if>",
            "GROUP BY ",
            "r.inviter_id,",
            "<if test='recruitStatus!=null and recruitStatus==0'>",
            "DATE(r.created_at)",
            "</if>",
            "<if test='recruitStatus!=null and recruitStatus==1'>",
            "DATE(r.invite_date_time)",
            "</if>",
            "<if test='recruitStatus!=null and (recruitStatus==2 or recruitStatus==3)'>",
            "DATE(r.is_pass_time)",
            "</if>",
            "<if test='recruitStatus!=null and recruitStatus==7'>",
            "DATE(r.give_up_time)",
            "</if>",
            ") AS subquery",
            "WHERE ",
            "subquery.ranking = 1",
            "ORDER BY ",
            "subquery.inviter_id",
            "</script>"})
    public List<RecruitList> getRecruitCount(@Param("recruitStatus") String recruitStatus);

    @Select({"<script>",
            "SELECT ",
            "subquery.inviter_id,",
            "subquery.teacher_name,",
            "subquery.try_date AS max_date,",
            "subquery.daily_try_count AS max_count",
            "FROM (",
            "SELECT ",
            "r.inviter_id,",
            "s.name AS teacher_name,",

//            "<if test='recruitStatus!=null and recruitStatus==4'>",
//            "atj.try_date,",
//            "</if>",
            "<if test='recruitStatus!=null and recruitStatus==4'>",
            " DATE(atj.try_date) AS try_date,",
            "</if>",
            "<if test='recruitStatus!=null and (recruitStatus==5 or recruitStatus==6)'>",
            "DATE(atj.try_audit_date)AS try_date,",
            "</if>",
            "COUNT(atj.id) AS daily_try_count,",
            "<if test='recruitStatus!=null and recruitStatus==4'>",
            "RANK() OVER (PARTITION BY r.inviter_id ORDER BY COUNT(atj.id) DESC, DATE(atj.try_date)) AS ranking",
            "</if>",
            "<if test='recruitStatus!=null and (recruitStatus==5 or recruitStatus==6)'>",
            "RANK() OVER (PARTITION BY r.inviter_id ORDER BY COUNT(atj.id) DESC, DATE(atj.try_audit_date)) AS ranking",
            "</if>",
            "FROM ",
            "adms_recruit r",
            "LEFT JOIN staff s ON s.id = r.inviter_id",
            "LEFT JOIN adms_try_job atj ON r.id = atj.recruit_id",
            "WHERE ",
            "<if test='recruitStatus!=null and  recruitStatus==4'>",
            "atj.try_date IS NOT NULL",
            "</if>",
            "<if test='recruitStatus!=null and  (recruitStatus==5 or recruitStatus==6)'>",
            "atj.try_audit_date IS NOT NULL",
            "</if>",
            "<if test='recruitStatus!=null and  recruitStatus==5'>",
            "AND atj.pass_type= 1",
            "</if>",
            "<if test='recruitStatus!=null and  recruitStatus==6'>",
            "AND atj.pass_type= 2",
            "</if>",
            "GROUP BY ",
            "r.inviter_id,",
            "<if test='recruitStatus!=null and  recruitStatus==4'>",
            "atj.try_date",
            "</if>",
            "<if test='recruitStatus!=null and  (recruitStatus==5 or recruitStatus==6)'>",
            "DATE(atj.try_audit_date)",
            "</if>",
            ") AS subquery",
            "WHERE ",
            "subquery.ranking = 1",
            "ORDER BY ",
            "subquery.inviter_id",
            "</script>"})
    public List<RecruitList> getTryJobCount(@Param("recruitStatus") String recruitStatus);

/*    @Select({"<script>",
            "SELECT",
            "s.id AS inviter_id,",
            "s.name AS teacher_name,",
            "drc.date AS createdAt,",
            "drc.daily_recruit_count AS recruitCount,",
            "COUNT(CASE WHEN r.invite_date_time BETWEEN #{startDate} AND #{endDate} THEN 1 END) AS interviewCount,",
            "COUNT(CASE WHEN r.is_pass_time BETWEEN #{startDate} AND #{endDate} AND r.pass_type = 1 THEN 1 END) AS interviewPassCount,",
            "COUNT(CASE WHEN r.is_pass_time BETWEEN #{startDate} AND #{endDate} AND r.pass_type = 2 THEN 1 END) AS interviewFailCount",
            "FROM",
            "staff s",
            "JOIN (",
            "SELECT",
            "r.inviter_id,",

            "<if test='recruitStatus!=null and recruitStatus==1'>",
            "DATE(r.invite_date_time) AS date,",
            "</if>",
            "<if test='recruitStatus!=null and recruitStatus==0'>",
            "DATE(r.created_at) AS date,",
            "</if>",

            "COUNT(*) AS daily_recruit_count",
            "FROM",
            "adms_recruit r",
            "WHERE",

            "<if test='recruitStatus!=null and recruitStatus==1'>",
            "r.invite_date_time BETWEEN #{startDate} AND #{endDate}",
            "</if>",
            "<if test='recruitStatus!=null and recruitStatus==0'>",
            "r.created_at BETWEEN #{startDate} AND #{endDate}",
            "</if>",

            "GROUP BY",
            "r.inviter_id,",

            "<if test='recruitStatus!=null and recruitStatus==1'>",
            "DATE(r.invite_date_time)",
            "</if>",
            "<if test='recruitStatus!=null and recruitStatus==0'>",
            "DATE(r.created_at)",
            "</if>",

            ") AS drc ON s.id = drc.inviter_id",
            "JOIN (",
            "SELECT",
            "inviter_id,",
            "MAX(daily_recruit_count) AS max_daily_recruit_count",
            "FROM (",
            "SELECT",
            "r.inviter_id,",

            "<if test='recruitStatus!=null and recruitStatus==1'>",
            "DATE(r.invite_date_time) AS date,",
            "</if>",
            "<if test='recruitStatus!=null and recruitStatus==0'>",
            "DATE(r.created_at) AS date,",
            "</if>",


            "COUNT(*) AS daily_recruit_count",
            "FROM",
            "adms_recruit r",
            "WHERE",

            "<if test='recruitStatus!=null and recruitStatus==1'>",
            "r.invite_date_time BETWEEN #{startDate} AND #{endDate}",
            "</if>",
            "<if test='recruitStatus!=null and recruitStatus==0'>",
            "r.created_at BETWEEN #{startDate} AND #{endDate}",
            "</if>",

            "GROUP BY",
            "r.inviter_id,",

            "<if test='recruitStatus!=null and recruitStatus==1'>",
            "DATE(r.invite_date_time)",
            "</if>",
            "<if test='recruitStatus!=null and recruitStatus==0'>",
            "DATE(r.created_at)",
            "</if>",

            ") AS subquery",
            "GROUP BY",
            "inviter_id",
            ") AS mdr ON drc.inviter_id = mdr.inviter_id AND drc.daily_recruit_count = mdr.max_daily_recruit_count",
            "LEFT JOIN adms_recruit r ON s.id = r.inviter_id",
            "WHERE",
            "s.deleted = 0",
            "GROUP BY",
            "s.id,",
            "s.name,",
            "drc.date,",
            "drc.daily_recruit_count",
            "ORDER BY",
            "s.id",
            "</script>"})
    public List<RecruitList> getRecruitCount(@Param("startDate") String startDate, @Param("endDate") String endDate,@Param("recruitStatus") String recruitStatus);*/

   /**试岗、试岗通过、试岗淘汰详情*/
   @Select({"<script>",
           "SELECT " ,
           "r.id,r.NAME,r.inviter_id,r.phone,r.created_at,tj.try_date AS tryDate,tj.try_audit_date AS tryAuditDate" ,
           "FROM",
           "adms_try_job tj",
           "LEFT JOIN adms_recruit r ON tj.recruit_id = r.id ",
           "WHERE",
           " 1= 1",
           "<if test='inviterId!=null and inviterId!=&apos;&apos;'>",
           "AND r.inviter_id = #{inviterId}",
           "</if>",
           "<if test='recruitStatus!=null and recruitStatus==4'>",
           "AND tj.try_date BETWEEN #{startDate} AND #{endDate}",
           "</if>",
           "<if test='recruitStatus!=null and recruitStatus==5'>",
           "AND tj.pass_type = 1",
           "AND tj.try_audit_date BETWEEN #{startDate} AND #{endDate}",
           "</if>",
           "<if test='recruitStatus!=null and recruitStatus==6'>",
           "AND tj.pass_type = 2",
           "AND tj.try_audit_date BETWEEN #{startDate} AND #{endDate}",
           "</if>",
           "</script>"})
    public IPage<Recruit>  dataCountTryJobDetail(IPage<?> page, @Param("startDate") String startDate,@Param("endDate") String endDate,@Param("inviterId") String inviterId,@Param("recruitStatus") Integer recruitStatus);

    @Update("update adms_recruit set invite_date_time= #{inviteDateTime},  status = #{status}, result_status = #{resultStatus},interview_status = #{interviewStatus}, turn_down_content = #{turnDownContent},is_arrive = #{isArrive}, interview_status = #{interviewStatus}  where id = ${id} ")
    public Integer updateRecruit(@Param("id") String id, @Param("status") Integer status, @Param("inviteDateTime") String inviteDateTime, @Param("resultStatus") Integer resultStatus, @Param("turnDownContent") String turnDownContent, @Param("isArrive") Integer isArrive, @Param("interviewStatus") Integer interviewStatus);


    @Select({"<script>",
            "SELECT ",
            "r.id AS inviter_id,",
            "r.name AS name,",
            "r.phone AS phone,",
            "<when test='recruitStatus == 0'>",
            "r.created_at AS max_date",
            "</when>",
            "<when test='recruitStatus == 1'>",
            "r.invite_date_time AS max_date",
            "</when>",
            "<when test='recruitStatus == 2 or recruitStatus == 3'>",
            "r.is_pass_time AS max_date",
            "</when>",
            "<when test='recruitStatus == 7'>",
            "r.give_up_time AS max_date",
            "</when>",
            "FROM ",
            "adms_recruit r",
            "JOIN (",
            "    SELECT ",
            "<when test='recruitStatus == 0'>",
            "        DATE(r.created_at) AS recruit_date",
            "</when>",
            "<when test='recruitStatus == 1'>",
            "        DATE(r.invite_date_time) AS recruit_date",
            "</when>",
            "<when test='recruitStatus == 2 or recruitStatus == 3'>",
            "        DATE(r.is_pass_time) AS recruit_date",
            "</when>",
            "<when test='recruitStatus == 7'>",
            "        DATE(r.give_up_time) AS recruit_date",
            "</when>",
            "    FROM ",
            "        adms_recruit r",
            "    WHERE ",
            "        r.inviter_id = #{inviterId}",
            "<when test='recruitStatus == 1'>",
            "AND invite_date_time is not null",
            "</when>",
            "<when test='recruitStatus == 2 or recruitStatus == 3'>",
            "AND is_pass_time is not null",
            "</when>",
            "<when test='recruitStatus == 7'>",
            "AND give_up_time is not null",
            "</when>",
            "    GROUP BY ",
            "<when test='recruitStatus == 0'>",
            "        DATE(r.created_at)",
            "</when>",
            "<when test='recruitStatus == 1'>",
            "        DATE(r.invite_date_time)",
            "</when>",
            "<when test='recruitStatus == 2 or recruitStatus == 3'>",
            "        DATE(r.is_pass_time)",
            "</when>",
            "<when test='recruitStatus == 7'>",
            "        DATE(r.give_up_time)",
            "</when>",
            "    ORDER BY ",
            "<when test='recruitStatus == 0 or recruitStatus == 1 or recruitStatus == 7'>",
            "        COUNT(r.id) DESC",
            "</when>",
            "<when test='recruitStatus == 2 '>",
            "        SUM(CASE WHEN r.pass_type = 1 THEN 1 ELSE 0 END) DESC",
            "</when>",
            "<when test='recruitStatus == 3'>",
            "        SUM(CASE WHEN r.pass_type = 2 THEN 1 ELSE 0 END) DESC",
            "</when>",
            "    LIMIT 1",
            ") AS max_recruit_dates ON " ,
            "<when test='recruitStatus == 0'>",
            "DATE(r.created_at) = max_recruit_dates.recruit_date",
            "</when>",
            "<when test='recruitStatus == 1'>",
            "DATE(r.invite_date_time) = max_recruit_dates.recruit_date",
            "</when>",
            "<when test='recruitStatus == 2 or recruitStatus == 3'>",
            "DATE(r.is_pass_time) = max_recruit_dates.recruit_date",
            "</when>",
            "<when test='recruitStatus == 7'>",
            "DATE(r.give_up_time) = max_recruit_dates.recruit_date",
            "</when>",
            "WHERE ",
            "r.inviter_id = #{inviterId}",
            "<when test='recruitStatus == 2 '>",
            "AND r.pass_type =1",
            "</when>",
            "<when test='recruitStatus == 3'>",
            "AND r.pass_type =2",
            "</when>",

            "</script>"})
    public IPage<Recruit>  oneDayCountDetail(IPage<?> page, @Param("inviterId") String inviterId,@Param("recruitStatus") Integer recruitStatus);

    @Select({"<script>",
            "SELECT ",
            "ar.inviter_id,",
            "ar.name AS name,",
            "ar.phone AS phone,",
            "<when test='recruitStatus == 4 '>",
            "atj.try_date AS max_date",
            "</when>",
            "<when test='recruitStatus == 5 or recruitStatus == 6'>",
            "DATE(atj.try_audit_date) AS max_date",
            "</when>",
            "FROM ",
            "adms_try_job atj",
            "JOIN (",
            "    SELECT ",
            "<when test='recruitStatus == 4 '>",
            "        atj_sub.try_date",
            "</when>",
            "    <when test='recruitStatus == 5 or recruitStatus == 6'>",
            "        DATE(atj_sub.try_audit_date) AS try_date",
            "</when>",
            "    FROM ",
            "        adms_recruit r_sub",
            "        LEFT JOIN adms_try_job atj_sub ON r_sub.id = atj_sub.recruit_id",
            "    WHERE ",
            "        r_sub.inviter_id = #{inviterId}",
            "<when test='recruitStatus == 4 '>",
            "        AND atj_sub.try_date IS NOT NULL",
            "</when>",
            "<when test='recruitStatus == 5 '>",
            "    AND atj_sub.try_audit_date IS NOT NULL",
            "    AND atj_sub.pass_type = 1",
            "</when>",
            "    <when test='recruitStatus == 6'>",
            "    AND atj_sub.try_audit_date IS NOT NULL",
            "    AND atj_sub.pass_type = 2",
            "</when>",
            "    GROUP BY ",
            "r_sub.inviter_id,",
            "<when test='recruitStatus == 4 '>",
            "        atj_sub.try_date",
            "</when>",
            "    <when test='recruitStatus == 5 or recruitStatus == 6'>",
            "        DATE(atj_sub.try_audit_date) ",
            "</when>",
            "    ORDER BY ",
            "        COUNT(atj_sub.id) DESC,",
            "<when test='recruitStatus == 4 '>",
            "        atj_sub.try_date",
            "</when>",
            "<when test='recruitStatus == 5 or recruitStatus == 6'>",
            "        DATE(atj_sub.try_audit_date) ",
            "</when>",
            "    LIMIT 1",
            ") AS max_try_dates ON " ,
            "<when test='recruitStatus == 4 '>",
            "DATE(atj.try_date) = max_try_dates.try_date",
            "</when>",
            "<when test='recruitStatus == 5 or recruitStatus == 6'>",
            "DATE(atj.try_audit_date) = max_try_dates.try_date",
            "</when>",
            "JOIN adms_recruit ar ON atj.recruit_id = ar.id",
            "WHERE ",
            "ar.inviter_id = #{inviterId}",
            "<when test='recruitStatus == 5 '>",
            "AND atj.pass_type = 1",
            "</when>",
            "<when test='recruitStatus == 6'>",
            "AND atj.pass_type = 2",
            "</when>",

            "</script>"})
    public IPage<Recruit>  oneDayCountTryJobDetail(IPage<?> page, @Param("inviterId") String inviterId,@Param("recruitStatus") Integer recruitStatus);


    @Select({"<script>",
            "SELECT ",
            "subquery.inviter_id,",
            "subquery.teacher_name,",
            "subquery.week_start_date AS max_week_start,",
            "subquery.week_end_date AS max_week_end,",
            "subquery.weekly_recruit_count AS max_count ",
            "FROM (",
            "    SELECT ",
            "        r.inviter_id,",
            "        s.name AS teacher_name,",
            "<when test='recruitStatus == 0'>",
            "        DATE_SUB(DATE(r.created_at), INTERVAL WEEKDAY(DATE(r.created_at)) DAY) AS week_start_date,",
            "        DATE_ADD(DATE_SUB(DATE(r.created_at), INTERVAL WEEKDAY(DATE(r.created_at)) DAY), INTERVAL 6 DAY) AS week_end_date,",
            "        COUNT(r.id) AS weekly_recruit_count,",
            "        RANK() OVER (PARTITION BY r.inviter_id ORDER BY COUNT(r.id) DESC, DATE_SUB(DATE(r.created_at), INTERVAL WEEKDAY(DATE(r.created_at)) DAY)) AS ranking ",
            "</when>",
            "<when test='recruitStatus == 1'>",
            "        DATE_SUB(DATE(r.invite_date_time), INTERVAL WEEKDAY(DATE(r.invite_date_time)) DAY) AS week_start_date,",
            "        DATE_ADD(DATE_SUB(DATE(r.invite_date_time), INTERVAL WEEKDAY(DATE(r.invite_date_time)) DAY), INTERVAL 6 DAY) AS week_end_date,",
            "        COUNT(r.id) AS weekly_recruit_count,",
            "        RANK() OVER (PARTITION BY r.inviter_id ORDER BY COUNT(r.id) DESC, DATE_SUB(DATE(r.invite_date_time), INTERVAL WEEKDAY(DATE(r.invite_date_time)) DAY)) AS ranking ",
            "</when>",
            "<when test='recruitStatus == 7'>",
            "        DATE_SUB(DATE(r.give_up_time), INTERVAL WEEKDAY(DATE(r.give_up_time)) DAY) AS week_start_date,",
            "        DATE_ADD(DATE_SUB(DATE(r.give_up_time), INTERVAL WEEKDAY(DATE(r.give_up_time)) DAY), INTERVAL 6 DAY) AS week_end_date,",
            "        COUNT(r.id) AS weekly_recruit_count,",
            "        RANK() OVER (PARTITION BY r.inviter_id ORDER BY COUNT(r.id) DESC, DATE_SUB(DATE(r.give_up_time), INTERVAL WEEKDAY(DATE(r.give_up_time)) DAY)) AS ranking ",
            "</when>",
            "<when test='recruitStatus == 2 or recruitStatus == 3'>",
            "        DATE_SUB(DATE(r.is_pass_time), INTERVAL WEEKDAY(DATE(r.is_pass_time)) DAY) AS week_start_date,",
            "        DATE_ADD(DATE_SUB(DATE(r.is_pass_time), INTERVAL WEEKDAY(DATE(r.is_pass_time)) DAY), INTERVAL 6 DAY) AS week_end_date,",
            "        COUNT(r.id) AS weekly_recruit_count,",
            "        RANK() OVER (PARTITION BY r.inviter_id ORDER BY COUNT(r.id) DESC, DATE_SUB(DATE(r.is_pass_time), INTERVAL WEEKDAY(DATE(r.is_pass_time)) DAY)) AS ranking ",
            "</when>",
            "    FROM ",
            "        adms_recruit r",
            "        LEFT JOIN staff s ON s.id = r.inviter_id ",
            "    WHERE ",
            "<when test='recruitStatus == 0'>",
            "        r.created_at IS NOT NULL",
            "</when>",
            "<when test='recruitStatus == 1'>",
            "        r.invite_date_time IS NOT NULL",
            "</when>",
            "<when test='recruitStatus == 7'>",
            "        r.give_up_time IS NOT NULL",
            "</when>",
            "<when test='recruitStatus == 2 '>",
            "        r.is_pass_time IS NOT NULL",
            "AND r.pass_type = 1",
            "</when>",
            "<when test='recruitStatus == 3'>",
            "        r.is_pass_time IS NOT NULL",
            "AND r.pass_type = 2",
            "</when>",
            "    GROUP BY ",
            "        r.inviter_id,",
            "<when test='recruitStatus == 0'>",
            "        DATE_SUB(DATE(r.created_at), INTERVAL WEEKDAY(DATE(r.created_at)) DAY)",
            "</when>",
            "<when test='recruitStatus == 1'>",
            "        DATE_SUB(DATE(r.invite_date_time), INTERVAL WEEKDAY(DATE(r.invite_date_time)) DAY)",
            "</when>",
            "<when test='recruitStatus == 7'>",
            "        DATE_SUB(DATE(r.give_up_time), INTERVAL WEEKDAY(DATE(r.give_up_time)) DAY)",
            "</when>",
            "<when test='recruitStatus == 2 or recruitStatus == 3'>",
            "        DATE_SUB(DATE(r.is_pass_time), INTERVAL WEEKDAY(DATE(r.is_pass_time)) DAY)",
            "</when>",
            ") AS subquery ",
            "WHERE ",
            "subquery.ranking = 1 ",
            "ORDER BY ",
            "subquery.inviter_id",
            "</script>"})
    public List<RecruitList> getRecruitCountByWeek(@Param("recruitStatus") String recruitStatus);


    @Select({"<script>",
            "SELECT ",
            "subquery.inviter_id,",
            "subquery.teacher_name,",
            "subquery.week_start_date AS max_week_start,",
            "subquery.week_end_date AS max_week_end,",
            "subquery.weekly_try_count AS max_count ",
            "FROM (",
            "    SELECT ",
            "        r.inviter_id,",
            "        s.name AS teacher_name,",
            "<when test='recruitStatus == 4'>",
            "        DATE_SUB(DATE(atj.try_date), INTERVAL WEEKDAY(DATE(atj.try_date)) DAY) AS week_start_date,",
            "        DATE_ADD(DATE_SUB(DATE(atj.try_date), INTERVAL WEEKDAY(DATE(atj.try_date)) DAY), INTERVAL 6 DAY) AS week_end_date,",
            "        COUNT(atj.id) AS weekly_try_count,",
            "        RANK() OVER (PARTITION BY r.inviter_id ORDER BY COUNT(atj.id) DESC, DATE_SUB(DATE(atj.try_date), INTERVAL WEEKDAY(DATE(atj.try_date)) DAY)) AS ranking ",
            "</when>",
            "<when test='recruitStatus == 5 or recruitStatus == 6'>",
            "        DATE_SUB(DATE(atj.try_audit_date), INTERVAL WEEKDAY(DATE(atj.try_audit_date)) DAY) AS week_start_date,",
            "        DATE_ADD(DATE_SUB(DATE(atj.try_audit_date), INTERVAL WEEKDAY(DATE(atj.try_audit_date)) DAY), INTERVAL 6 DAY) AS week_end_date,",
            "        COUNT(atj.id) AS weekly_try_count,",
            "        RANK() OVER (PARTITION BY r.inviter_id ORDER BY COUNT(atj.id) DESC, DATE_SUB(DATE(atj.try_audit_date), INTERVAL WEEKDAY(DATE(atj.try_audit_date)) DAY)) AS ranking ",
            "</when>",
            "    FROM ",
            "        adms_recruit r",
            "        LEFT JOIN staff s ON s.id = r.inviter_id",
            "        LEFT JOIN adms_try_job atj ON r.id = atj.recruit_id ",
            "    WHERE",
            "<when test='recruitStatus == 4'>",
            "        atj.try_date IS NOT NULL ",
            "</when>",
            "<when test='recruitStatus == 5 '>",
            "        atj.try_audit_date IS NOT NULL ",
            "AND atj.pass_type = 1",
            "</when>",
            "<when test='recruitStatus == 6'>",
            "        atj.try_audit_date IS NOT NULL ",
            "AND atj.pass_type = 2",
            "</when>",
            "    GROUP BY",
            "        r.inviter_id,",
            "<when test='recruitStatus == 4'>",
            "        DATE_SUB(DATE(atj.try_date), INTERVAL WEEKDAY(DATE(atj.try_date)) DAY)",
            "</when>",
            "<when test='recruitStatus == 5 or recruitStatus == 6'>",
            "        DATE_SUB(DATE(atj.try_audit_date), INTERVAL WEEKDAY(DATE(atj.try_audit_date)) DAY)",
            "</when>",
            ") AS subquery ",
            "WHERE ",
            "subquery.ranking = 1 ",
            "ORDER BY ",
            "subquery.inviter_id",
            "</script>"})
    public List<RecruitList> getTryJobCountByWeek(@Param("recruitStatus") String recruitStatus);


    @Select({"<script>",
            "SELECT ",
            "ar.name AS name,",
            "ar.phone AS phone,",
            "<when test='recruitStatus == 0'>",
            "DATE(ar.created_at) AS max_date",
            "</when>",
            "<when test='recruitStatus == 1'>",
            "DATE(ar.invite_date_time) AS max_date",
            "</when>",
            "<when test='recruitStatus == 7'>",
            "DATE(ar.give_up_time) AS max_date",
            "</when>",
            "<when test='recruitStatus == 2 or recruitStatus == 3'>",
            "DATE(ar.is_pass_time) AS max_date",
            "</when>",
            "FROM ",
            "adms_recruit ar",
            "JOIN (",
            "    SELECT ",
            "<when test='recruitStatus == 0'>",
            "        DATE_SUB(DATE(r_sub.created_at), INTERVAL WEEKDAY(DATE(r_sub.created_at)) DAY) AS week_start_date",
            "</when>",
            "<when test='recruitStatus == 1'>",
            "        DATE_SUB(DATE(r_sub.invite_date_time), INTERVAL WEEKDAY(DATE(r_sub.invite_date_time)) DAY) AS week_start_date",
            "</when>",
            "<when test='recruitStatus == 7'>",
            "        DATE_SUB(DATE(r_sub.give_up_time), INTERVAL WEEKDAY(DATE(r_sub.give_up_time)) DAY) AS week_start_date",
            "</when>",
            "<when test='recruitStatus == 2 or recruitStatus == 3'>",
            "        DATE_SUB(DATE(r_sub.is_pass_time), INTERVAL WEEKDAY(DATE(r_sub.is_pass_time)) DAY) AS week_start_date",
            "</when>",
            "    FROM ",
            "        adms_recruit r_sub",
            "        LEFT JOIN staff s_sub ON s_sub.id = r_sub.inviter_id",
            "    WHERE ",
            "        r_sub.inviter_id = #{inviterId}",
            "<when test='recruitStatus == 0'>",
            "        AND r_sub.created_at IS NOT NULL",
            "</when>",
            "<when test='recruitStatus == 1'>",
            "        AND r_sub.invite_date_time IS NOT NULL",
            "</when>",
            "<when test='recruitStatus == 7'>",
            "        AND r_sub.give_up_time IS NOT NULL",
            "</when>",
            "<when test='recruitStatus == 2'>",
            "        AND r_sub.is_pass_time IS NOT NULL",
            "        AND r_sub.pass_type = 1",
            "</when>",
            "<when test='recruitStatus == 3'>",
            "        AND r_sub.is_pass_time IS NOT NULL",
            "        AND r_sub.pass_type = 2",
            "</when>",
            "    GROUP BY ",
            "        r_sub.inviter_id,",
            "<when test='recruitStatus == 0'>",
            "        DATE_SUB(DATE(r_sub.created_at), INTERVAL WEEKDAY(DATE(r_sub.created_at)) DAY)",
            "</when>",
            "<when test='recruitStatus == 1'>",
            "        DATE_SUB(DATE(r_sub.invite_date_time), INTERVAL WEEKDAY(DATE(r_sub.invite_date_time)) DAY)",
            "</when>",
            "<when test='recruitStatus == 7'>",
            "        DATE_SUB(DATE(r_sub.give_up_time), INTERVAL WEEKDAY(DATE(r_sub.give_up_time)) DAY)",
            "</when>",
            "<when test='recruitStatus == 2 or recruitStatus == 3'>",
            "        DATE_SUB(DATE(r_sub.is_pass_time), INTERVAL WEEKDAY(DATE(r_sub.is_pass_time)) DAY)",
            "</when>",
            "    ORDER BY ",
            "        COUNT(r_sub.id) DESC,",
            "<when test='recruitStatus == 0'>",
            "        DATE_SUB(DATE(r_sub.created_at), INTERVAL WEEKDAY(DATE(r_sub.created_at)) DAY)",
            "</when>",
            "<when test='recruitStatus == 1'>",
            "        DATE_SUB(DATE(r_sub.invite_date_time), INTERVAL WEEKDAY(DATE(r_sub.invite_date_time)) DAY)",
            "</when>",
            "<when test='recruitStatus == 7'>",
            "        DATE_SUB(DATE(r_sub.give_up_time), INTERVAL WEEKDAY(DATE(r_sub.give_up_time)) DAY)",
            "</when>",
            "<when test='recruitStatus == 2 or recruitStatus == 3'>",
            "        DATE_SUB(DATE(r_sub.is_pass_time), INTERVAL WEEKDAY(DATE(r_sub.is_pass_time)) DAY)",
            "</when>",
            "    LIMIT 1",
            ") AS max_week ON ",

            "<when test='recruitStatus == 0'>",
                    "DATE_SUB(DATE(ar.created_at), INTERVAL WEEKDAY(DATE(ar.created_at)) DAY) = max_week.week_start_date",
            "</when>",
            "<when test='recruitStatus == 1'>",
                    "DATE_SUB(DATE(ar.invite_date_time), INTERVAL WEEKDAY(DATE(ar.invite_date_time)) DAY) = max_week.week_start_date",
            "</when>",
            "<when test='recruitStatus == 7'>",
                    "DATE_SUB(DATE(ar.give_up_time), INTERVAL WEEKDAY(DATE(ar.give_up_time)) DAY) = max_week.week_start_date",
            "</when>",
            "<when test='recruitStatus == 2 or recruitStatus == 3'>",
                    "DATE_SUB(DATE(ar.is_pass_time), INTERVAL WEEKDAY(DATE(ar.is_pass_time)) DAY) = max_week.week_start_date",
            "</when>",
            "WHERE ",
            "ar.inviter_id = #{inviterId}",
            "<when test='recruitStatus == 2'>",
            "    AND ar.pass_type = 1",
            "</when>",
            "<when test='recruitStatus == 3'>",
            "    AND ar.pass_type = 2",
            "</when>",
            "</script>"})
    public IPage<Recruit>  oneWeekCountDetail(IPage<?> page, @Param("inviterId") String inviterId,@Param("recruitStatus") Integer recruitStatus);

    @Select({"<script>",
            "SELECT ",
            "ar.name AS name,",
            "ar.phone AS phone,",

            "<when test='recruitStatus == 4'>",
            "DATE(atj.try_date) AS max_date",
            "</when>",
            "<when test='recruitStatus == 5 or recruitStatus == 6'>",
            "DATE(atj.try_audit_date) AS max_date",
            "</when>",

            "FROM ",
            "adms_try_job atj",
            "JOIN adms_recruit ar ON atj.recruit_id = ar.id",
            "JOIN (",
            "    SELECT ",

            "<when test='recruitStatus == 4'>",
            "        DATE_SUB(DATE(atj_sub.try_date), INTERVAL WEEKDAY(DATE(atj_sub.try_date)) DAY) AS week_start_date",
            "</when>",
            "    <when test='recruitStatus == 5 or recruitStatus == 6'>",
            "        DATE_SUB(DATE(atj_sub.try_audit_date), INTERVAL WEEKDAY(DATE(atj_sub.try_audit_date)) DAY) AS week_start_date",
            "</when>",

            "    FROM ",
            "        adms_recruit r_sub",
            "        LEFT JOIN adms_try_job atj_sub ON r_sub.id = atj_sub.recruit_id",
            "    WHERE ",
            "        r_sub.inviter_id = #{inviterId}",

            "<when test='recruitStatus == 4'>",
            "        AND atj_sub.try_date IS NOT NULL",
            "</when>",
            "    <when test='recruitStatus == 5 '>",
            "        AND atj_sub.try_audit_date IS NOT NULL",
            "        AND atj_sub.pass_type = 1",
            "</when>",
            "    <when test='recruitStatus == 6'>",
            "        AND atj_sub.try_audit_date IS NOT NULL",
            "        AND atj_sub.pass_type = 2",
            "</when>",

            "    GROUP BY ",
            "        r_sub.inviter_id,",

            "<when test='recruitStatus == 4'>",
            "        DATE_SUB(DATE(atj_sub.try_date), INTERVAL WEEKDAY(DATE(atj_sub.try_date)) DAY)",
            "</when>",
            "<when test='recruitStatus == 5 or recruitStatus == 6'>",
            "        DATE_SUB(DATE(atj_sub.try_audit_date), INTERVAL WEEKDAY(DATE(atj_sub.try_audit_date)) DAY)",
            "</when>",

            "    ORDER BY ",
            "        COUNT(atj_sub.id) DESC,",

            "<when test='recruitStatus == 4'>",
            "        DATE_SUB(DATE(atj_sub.try_date), INTERVAL WEEKDAY(DATE(atj_sub.try_date)) DAY)",
            "</when>",
            "<when test='recruitStatus == 5 or recruitStatus == 6'>",
            "        DATE_SUB(DATE(atj_sub.try_audit_date), INTERVAL WEEKDAY(DATE(atj_sub.try_audit_date)) DAY)",
            "</when>",

            "    LIMIT 1",
            ") AS max_week ON ",

            "<when test='recruitStatus == 4'>",
                    "DATE_SUB(DATE(atj.try_date), INTERVAL WEEKDAY(DATE(atj.try_date)) DAY) = max_week.week_start_date",
            "</when>",
            "<when test='recruitStatus == 5 or recruitStatus == 6'>",
                    "DATE_SUB(DATE(atj.try_audit_date), INTERVAL WEEKDAY(DATE(atj.try_audit_date)) DAY) = max_week.week_start_date",
            "</when>",
            "WHERE ",
            "ar.inviter_id = #{inviterId}",
            "<when test='recruitStatus == 5'>",
            "    AND atj.pass_type = 1",
            "</when>",
            "<when test='recruitStatus == 6'>",
            "    AND atj.pass_type = 2",
            "</when>",
            "</script>"})
    public IPage<Recruit>  oneWeekTryJobCountDetail(IPage<?> page, @Param("inviterId") String inviterId,@Param("recruitStatus") Integer recruitStatus);


    @Select({"<script>",
            "WITH teacher_with as (select id,name,IF(role_admin.admin_id is null,1,2) as is_person from staff LEFT JOIN role_admin on staff.id = role_admin.admin_id and role_admin.role_id = 1568160915052400644 )",
            "SELECT",
            "r.id,",
            "r.inviter_id,",
            "s.name AS teacherName,",
            "COUNT(CASE WHEN r.created_at BETWEEN #{startDate} AND #{endDate} THEN 1 END) AS recruitCount, ",
            "COUNT(CASE WHEN r.invite_date_time BETWEEN #{startDate} AND #{endDate} AND r.is_arrive =1 THEN 1 END ) AS recruitInterviewCount, ",
            "COUNT(CASE WHEN r.created_at BETWEEN #{startDate} AND #{endDate} AND (r.is_arrive IS null OR r.is_arrive = 0) THEN 1 END ) AS notRecruitInterviewCount, ",
            "COUNT( CASE WHEN r.is_pass_time BETWEEN #{startDate} AND #{endDate} AND r.pass_type = 1 THEN 1 END ) AS recruitPassCount,",
            "COUNT( CASE WHEN r.is_pass_time BETWEEN #{startDate} AND #{endDate} AND r.pass_type = 2 THEN 1 END ) AS recruitNoPassCount",
            "FROM",
            "adms_recruit r",
            " JOIN staff s ON s.id = r.inviter_id ",
            " JOIN adms_station_needs t1 ON t1.id = r.station_needs_id",
            "INNER JOIN teacher_with as t2 on r.inviter_id = t2.id",
            "WHERE",
            "1 = 1",
            "<if test='theirType != null and theirType != &apos;&apos;'>",
                "AND t1.type = ${theirType}",
            "</if>",
            "<if test='subjectsId != null and subjectsId != &apos;&apos;'>",
                "AND t1.subjects_id IN ( ${subjectsId} )",
            "</if>",
            "<if test='type != null and type != &apos;&apos;'>",
            "AND t2.is_person = ${type}",
            "</if>",
            "<if test='departmentId != null and departmentId != &apos;&apos;'>",
            "AND t1.department_id = ${departmentId}",
            "</if>",
            "<if test='stationsId != null and stationsId != &apos;&apos;'>",
            "AND t1.station_id IN ( ${stationsId} )",
            "</if>",
            "<if test='labourId != null and labourId != &apos;&apos;'>",
            "AND t1.labour_id = ${labourId}",
            "</if>",
            "<if test='yearPart != null and yearPart != &apos;&apos;'>",
            "AND r.year_part = ${yearPart}",
            "</if>",
            "<if test='quarterNum != null and quarterNum != &apos;&apos;'>",
            "AND r.quarter_num = ${quarterNum}",
            "</if>",
            "GROUP BY",
            "r.inviter_id",
            "</script>"})
    public List<RecruitList> resumeData(@Param("startDate") String startDate,
                                        @Param("endDate") String endDate,
                                        @Param("departmentId") String departmentId,
                                        @Param("stationsId") String stationsId,
                                        @Param("labourId") String labourId,
                                        @Param("type") String type,
                                        @Param("yearPart") String yearPart,
                                        @Param("quarterNum") String quarterNum,
                                        @Param("dateType") String dateType,
                                        @Param("theirType") String theirType,
                                        @Param("subjectsId") String subjectsId
    );



    @Select({"<script>",
            "WITH teacher_with as (select id,name,IF(role_admin.admin_id is null,1,2) as is_person from staff LEFT JOIN role_admin on staff.id = role_admin.admin_id and role_admin.role_id = 1568160915052400644 )",
            "SELECT",
            "r.`name`,",
            "r.phone,",
            "r.invite_date_time,",
            "r.is_pass_time,",
            "t1.NAME AS stationName,",
            "r.created_at",
            "FROM",
            "adms_recruit r",
            " JOIN staff s ON s.id = r.inviter_id ",
            " JOIN adms_station_needs t1 ON t1.id = r.station_needs_id",
            "INNER JOIN teacher_with as t2 on r.inviter_id = t2.id",
            "WHERE",
            "1 = 1",
            "<if test='theirType != null and theirType != &apos;&apos;'>",
                "AND t1.type = ${theirType}",
            "</if>",
            "<if test='subjectsId != null and subjectsId != &apos;&apos;'>",
                "AND t1.subjects_id IN ( ${subjectsId} )",
            "</if>",
            "<if test='type != null and type != &apos;&apos;'>",
            "AND t2.is_person = ${type}",
            "</if>",
            "<if test='departmentId != null and departmentId != &apos;&apos;'>",
            "AND t1.department_id = ${departmentId}",
            "</if>",
            "<if test='stationsId != null and stationsId != &apos;&apos;'>",
            "AND t1.station_id IN ( ${stationsId} )",
            "</if>",
            "<if test='labourId != null and labourId != &apos;&apos;'>",
            "AND t1.labour_id = ${labourId}",
            "</if>",
            "<if test='yearPart != null and yearPart != &apos;&apos;'>",
            "AND r.year_part = ${yearPart}",
            "</if>",
            "<if test='quarterNum != null and quarterNum != &apos;&apos;'>",
            "AND r.quarter_num = ${quarterNum}",
            "</if>",
            "AND r.inviter_id = ${inviterId}",
            "<if test='dateType != null and dateType != &apos;&apos; '>",
            //简历数据详情
                "<if test='dateType == 0 '>",
                "AND r.created_at BETWEEN #{startDate} AND #{endDate}",
                "</if>",

            //简历邀约量详情
            "<if test='dateType == 1'>",
                "<if test='recruitStatus == 0'>",
                    "AND r.created_at BETWEEN #{startDate} AND #{endDate}",
                "</if>",
                "<if test='recruitStatus == 1'>",
                "AND r.invite_date_time BETWEEN #{startDate} AND #{endDate} AND r.is_arrive =1",
                "</if>",
                "<if test='recruitStatus == 2'>",
                "AND r.created_at BETWEEN #{startDate} AND #{endDate} AND (r.is_arrive IS null OR r.is_arrive = 0)",
                "</if>",
            "</if>",
            //面试通过率详情
            "<if test='dateType == 2'>",
                "<if test='recruitStatus == 1'>",
                "AND r.invite_date_time BETWEEN #{startDate} AND #{endDate} AND r.is_arrive =1",
                "</if>",
                "<if test='recruitStatus == 3'>",
                "AND r.is_pass_time BETWEEN #{startDate} AND #{endDate} AND r.pass_type = 2",
                "</if>",
                "<if test='recruitStatus == 4'>",
                "AND r.is_pass_time BETWEEN #{startDate} AND #{endDate} AND r.pass_type = 1",
                "</if>",
            "</if>",

            "</if>",
            "</script>"})
    public IPage<Recruit> resumeDataDetails(IPage<?> page,
                                            @Param("startDate") String startDate,
                                            @Param("endDate") String endDate,
                                            @Param("departmentId") String departmentId,
                                            @Param("stationsId") String stationsId,
                                            @Param("labourId") String labourId,
                                            @Param("type") String type,
                                            @Param("inviterId") String inviterId,
                                            @Param("yearPart") String yearPart,
                                            @Param("quarterNum") String quarterNum,
                                            @Param("dateType") String dateType,
                                            @Param("recruitStatus") String recruitStatus,
                                            @Param("theirType") String theirType,
                                            @Param("subjectsId") String subjectsId);

    /**
     * 面试分析列表
     */
    @Select({"<script>",
            "SELECT",
            "grade_details_id,",
            "grade_details_name,",
            "COUNT( id ) AS not_pass_count ",
            "FROM (",
                "SELECT",
                "t1.id,",
                "t1.`name`,",
                "t3.id AS grade_details_id,",
                "t3.`name` AS grade_details_name,",
                "t4.grade,",
                "t4.option_text, ",
                "t5.name AS station_needs_name, ",
                "CASE WHEN EXISTS ( SELECT 1 FROM role_admin WHERE admin_id = t1.inviter_id AND role_id = 1568160915052400644 ) THEN 2 ELSE 1 END AS role_id ",
                "FROM",
                "adms_recruit AS t1",
                "INNER JOIN adms_estimate_details AS t2 ON t1.id = t2.recruit_id",
                "INNER JOIN adms_grade_details AS t3 ON t2.grade_details_id = t3.id ",
                "AND t3.deleted = 0",
                "INNER JOIN adms_grade_option AS t4 ON t2.option_id = t4.id ",
                "AND t4.deleted = 0",
                "INNER JOIN adms_station_needs AS t5 ON t1.station_needs_id = t5.id ",
                "WHERE",
                "t1.pass_type = 2 ",
                "AND t2.type = 1 ",
                "AND t4.option_text = '不通过' ",
                "<if test='departmentId != null and departmentId != &apos;&apos;'>",
                    "AND t5.department_id = ${departmentId}",
                "</if>",
                "<if test='stationsId != null and stationsId != &apos;&apos;'>",
                    "AND t5.station_id IN ( ${stationsId} )",
                "</if>",
                "<if test='labourId != null and labourId != &apos;&apos;'>",
                    "AND t5.labour_id IN ( ${labourId} )",
                "</if>",
                "<if test='yearPart != null and yearPart != &apos;&apos;'>",
                    "AND t1.year_part = ${yearPart}",
                "</if>",
                "<if test='quarterNum != null and quarterNum != &apos;&apos;'>",
                    "AND t1.quarter_num = ${quarterNum}",
                "</if>",
                "<if test='startDate != null and startDate != &apos;&apos; and endDate != null and endDate != &apos;&apos;'>",
                    "AND t1.created_at BETWEEN '${startDate}' AND '${endDate}' ",
                "</if>",
                "<if test='subjectsId != null and subjectsId != &apos;&apos;'>",
                    "AND t5.subjects_id IN ( ${subjectsId} )",
                "</if>",
                "<if test='theirType != null and theirType != &apos;&apos;'>",
                    "AND t5.type = ${theirType}",
                "</if>",
            ") AS t01 ",
            "WHERE 1=1",
            "<if test='type != null and type != &apos;&apos;'>",
                "AND role_id = ${type}",
            "</if>",
            "GROUP BY",
            "grade_details_id",
            "</script>"})
    List<RecruitAnalysisRs> getRecruitAnalysisList(@Param("yearPart") String yearPart,
                                                   @Param("quarterNum") String quarterNum,
                                                   @Param("startDate") String startDate,
                                                   @Param("endDate") String endDate,
                                                   @Param("stationsId") String stationsId,
                                                   @Param("departmentId") String departmentId,
                                                   @Param("subjectsId") String subjectsId,
                                                   @Param("labourId") String labourId,
                                                   @Param("type") String type,
                                                   @Param("theirType") String theirType
    );

    /**
     * 面试分析详情
     */
    @Select({"<script>",
            "SELECT * FROM (",
                "SELECT",
                "t1.id,",
                "t1.`name`,",
                "t3.id AS grade_details_id,",
                "t3.`name` AS grade_details_name,",
                "t4.grade,",
                "t4.option_text, ",
                "t5.name AS station_needs_name, ",
                "CASE WHEN EXISTS ( SELECT 1 FROM role_admin WHERE admin_id = t1.inviter_id AND role_id = 1568160915052400644 ) THEN 2 ELSE 1 END AS role_id ",
                "FROM",
                "adms_recruit AS t1",
                "INNER JOIN adms_estimate_details AS t2 ON t1.id = t2.recruit_id",
                "INNER JOIN adms_grade_details AS t3 ON t2.grade_details_id = t3.id ",
                "AND t3.deleted = 0",
                "INNER JOIN adms_grade_option AS t4 ON t2.option_id = t4.id ",
                "AND t4.deleted = 0",
                "INNER JOIN adms_station_needs AS t5 ON t1.station_needs_id = t5.id ",
                "WHERE",
                "t1.pass_type = 2 ",
                "AND t2.type = 1 ",
                "AND t4.option_text = '不通过' ",
                "<if test='departmentId != null and departmentId != &apos;&apos;'>",
                    "AND t5.department_id = ${departmentId}",
                "</if>",
                "<if test='stationsId != null and stationsId != &apos;&apos;'>",
                    "AND t5.station_id IN ( ${stationsId} )",
                "</if>",
                "<if test='labourId != null and labourId != &apos;&apos;'>",
                    "AND t5.labour_id IN ( ${labourId} )",
                "</if>",
                "<if test='yearPart != null and yearPart != &apos;&apos;'>",
                    "AND t1.year_part = ${yearPart}",
                "</if>",
                "<if test='quarterNum != null and quarterNum != &apos;&apos;'>",
                    "AND t1.quarter_num = ${quarterNum}",
                "</if>",
                "<if test='startDate != null and startDate != &apos;&apos; and endDate != null and endDate != &apos;&apos;'>",
                    "AND t1.created_at BETWEEN '${startDate}' AND '${endDate}' ",
                "</if>",
                "<if test='subjectsId != null and subjectsId != &apos;&apos;'>",
                    "AND t5.subjects_id IN ( ${subjectsId} )",
                "</if>",
                "<if test='gradeDetailsId != null and gradeDetailsId != &apos;&apos;'>",
                    "AND t3.id = ${gradeDetailsId} ",
                "</if>",
                "<if test='theirType != null and theirType != &apos;&apos;'>",
                    "AND t5.type = ${theirType}",
                "</if>",
            ")AS t",
            "WHERE 1=1",
            "<if test='type != null and type != &apos;&apos;'>",
                "AND role_id = ${type}",
            "</if>",
            "</script>"})
    IPage<RecruitAnalysisRs> getRecruitAnalysisDetails(IPage<RecruitAnalysisRs> page,
                                                       @Param("yearPart") String yearPart,
                                                       @Param("quarterNum") String quarterNum,
                                                       @Param("startDate") String startDate,
                                                       @Param("endDate") String endDate,
                                                       @Param("stationsId") String stationsId,
                                                       @Param("departmentId") String departmentId,
                                                       @Param("subjectsId") String subjectsId,
                                                       @Param("labourId") String labourId,
                                                       @Param("gradeDetailsId") String gradeDetailsId,
                                                       @Param("type") String type,
                                                       @Param("theirType") String theirType
    );

    /**
     * 试岗分析列表
     */
    @Select({"<script>",
            "SELECT",
            "grade_details_id,",
            "grade_details_name,",
            "COUNT( id ) AS not_pass_count ",
            "FROM (",
                "SELECT",
                "t1.id,",
                "t1.`name`,",
                "t3.id AS grade_details_id,",
                "t3.`name` AS grade_details_name,",
                "t4.grade,",
                "t4.option_text, ",
                "t5.name AS station_needs_name, ",
                "CASE WHEN EXISTS ( SELECT 1 FROM role_admin WHERE admin_id = t1.inviter_id AND role_id = 1568160915052400644 ) THEN 2 ELSE 1 END AS role_id ",
                "FROM",
                "adms_recruit AS t1",
                "INNER JOIN adms_estimate_details AS t2 ON t1.id = t2.recruit_id",
                "INNER JOIN adms_grade_details AS t3 ON t2.grade_details_id = t3.id ",
                "AND t3.deleted = 0",
                "INNER JOIN adms_grade_option AS t4 ON t2.option_id = t4.id ",
                "AND t4.deleted = 0",
                "INNER JOIN adms_try_job AS t6 ON t1.id = t6.recruit_id",
                "INNER JOIN adms_station_needs AS t5 ON t6.station_needs_id = t5.id ",
                "WHERE",
                "t6.pass_type = 2 ",
                "AND t2.type = 2 ",
                "AND t4.option_text = '不通过' ",
                "<if test='departmentId != null and departmentId != &apos;&apos;'>",
                    "AND t5.department_id = ${departmentId}",
                "</if>",
                "<if test='stationsId != null and stationsId != &apos;&apos;'>",
                    "AND t5.station_id IN ( ${stationsId} )",
                "</if>",
                "<if test='labourId != null and labourId != &apos;&apos;'>",
                    "AND t5.labour_id IN ( ${labourId} )",
                "</if>",
                "<if test='yearPart != null and yearPart != &apos;&apos;'>",
                    "AND t1.year_part = ${yearPart}",
                "</if>",
                "<if test='quarterNum != null and quarterNum != &apos;&apos;'>",
                    "AND t1.quarter_num = ${quarterNum}",
                "</if>",
                "<if test='startDate != null and startDate != &apos;&apos; and endDate != null and endDate != &apos;&apos;'>",
                    "AND t1.created_at BETWEEN '${startDate}' AND '${endDate}' ",
                "</if>",
                "<if test='subjectsId != null and subjectsId != &apos;&apos;'>",
                    "AND t5.subjects_id IN ( ${subjectsId} )",
                "</if>",
                "<if test='theirType != null and theirType != &apos;&apos;'>",
                    "AND t5.type = ${theirType}",
                "</if>",
            ") AS t01 ",
            "WHERE 1=1",
            "<if test='type != null and type != &apos;&apos;'>",
                "AND role_id = ${type}",
            "</if>",
            "GROUP BY",
            "grade_details_id",
            "</script>"})
    List<RecruitAnalysisRs> getTryJobAnalysisList(@Param("yearPart") String yearPart,
                                                  @Param("quarterNum") String quarterNum,
                                                  @Param("startDate") String startDate,
                                                  @Param("endDate") String endDate,
                                                  @Param("stationsId") String stationsId,
                                                  @Param("departmentId") String departmentId,
                                                  @Param("subjectsId") String subjectsId,
                                                  @Param("labourId") String labourId,
                                                  @Param("type") String type,
                                                  @Param("theirType") String theirType
    );

    /**
     * 试岗分析详情
     */
    @Select({"<script>",
            "SELECT * FROM (",
                "SELECT",
                "t1.id,",
                "t1.`name`,",
                "t3.id AS grade_details_id,",
                "t3.`name` AS grade_details_name,",
                "t4.grade,",
                "t4.option_text, ",
                "t5.name AS station_needs_name, ",
                "CASE WHEN EXISTS ( SELECT 1 FROM role_admin WHERE admin_id = t1.inviter_id AND role_id = 1568160915052400644 ) THEN 2 ELSE 1 END AS role_id ",
                "FROM",
                "adms_recruit AS t1",
                "INNER JOIN adms_estimate_details AS t2 ON t1.id = t2.recruit_id",
                "INNER JOIN adms_grade_details AS t3 ON t2.grade_details_id = t3.id ",
                "AND t3.deleted = 0",
                "INNER JOIN adms_grade_option AS t4 ON t2.option_id = t4.id ",
                "AND t4.deleted = 0",
                "INNER JOIN adms_try_job AS t6 ON t1.id = t6.recruit_id",
                "INNER JOIN adms_station_needs AS t5 ON t6.station_needs_id = t5.id ",
                "WHERE",
                "t6.pass_type = 2 ",
                "AND t2.type = 2 ",
                "AND t4.option_text = '不通过' ",
                "<if test='departmentId != null and departmentId != &apos;&apos;'>",
                    "AND t5.department_id = ${departmentId}",
                "</if>",
                "<if test='stationsId != null and stationsId != &apos;&apos;'>",
                    "AND t5.station_id IN ( ${stationsId} )",
                "</if>",
                "<if test='labourId != null and labourId != &apos;&apos;'>",
                    "AND t5.labour_id IN ( ${labourId} )",
                "</if>",
                "<if test='yearPart != null and yearPart != &apos;&apos;'>",
                    "AND t1.year_part = ${yearPart}",
                "</if>",
                "<if test='quarterNum != null and quarterNum != &apos;&apos;'>",
                    "AND t1.quarter_num = ${quarterNum}",
                "</if>",
                "<if test='startDate != null and startDate != &apos;&apos; and endDate != null and endDate != &apos;&apos;'>",
                    "AND t1.created_at BETWEEN '${startDate}' AND '${endDate}' ",
                "</if>",
                "<if test='subjectsId != null and subjectsId != &apos;&apos;'>",
                    "AND t5.subjects_id IN ( ${subjectsId} )",
                "</if>",
                "<if test='gradeDetailsId != null and gradeDetailsId != &apos;&apos;'>",
                    "AND t3.id = ${gradeDetailsId} ",
                "</if>",
                "<if test='theirType != null and theirType != &apos;&apos;'>",
                    "AND t5.type = ${theirType}",
                "</if>",
            ")AS t",
            "WHERE 1=1",
            "<if test='type != null and type != &apos;&apos;'>",
            "AND role_id = ${type}",
            "</if>",
            "</script>"})
    IPage<RecruitAnalysisRs> getTryJobAnalysisDetails(IPage<RecruitAnalysisRs> page,
                                                      @Param("yearPart") String yearPart,
                                                      @Param("quarterNum") String quarterNum,
                                                      @Param("startDate") String startDate,
                                                      @Param("endDate") String endDate,
                                                      @Param("stationsId") String stationsId,
                                                      @Param("departmentId") String departmentId,
                                                      @Param("subjectsId") String subjectsId,
                                                      @Param("labourId") String labourId,
                                                      @Param("gradeDetailsId") String gradeDetailsId,
                                                      @Param("type") String type,
                                                      @Param("theirType") String theirType
    );

    /**
     * 试用分析列表
     */
    @Select({"<script>",
            "SELECT",
            "grade_details_id,",
            "grade_details_name,",
            "COUNT( id ) AS not_pass_count ",
            "FROM (",
                "SELECT",
                "t1.id,",
                "t1.`name`,",
                "t3.id AS grade_details_id,",
                "t3.`name` AS grade_details_name,",
                "t4.grade,",
                "t4.option_text, ",
                "t5.name AS station_needs_name, ",
                "CASE WHEN EXISTS ( SELECT 1 FROM role_admin WHERE admin_id = t1.inviter_id AND role_id = 1568160915052400644 ) THEN 2 ELSE 1 END AS role_id ",
                "FROM",
                "adms_recruit AS t1",
                "INNER JOIN adms_estimate_details AS t2 ON t1.id = t2.recruit_id",
                "INNER JOIN adms_grade_details AS t3 ON t2.grade_details_id = t3.id ",
                "AND t3.deleted = 0",
                "INNER JOIN adms_grade_option AS t4 ON t2.option_id = t4.id ",
                "AND t4.deleted = 0",
                "INNER JOIN adms_try_job AS t6 ON t1.id = t6.recruit_id",
                "INNER JOIN adms_trial_form AS t7 ON t6.id = t7.try_job_id",
                "INNER JOIN adms_station_needs AS t5 ON t6.station_needs_id = t5.id ",
                "WHERE",
                "t7.pass_type = 2 ",
                "AND t2.type = 3 ",
                "AND t4.option_text = '不通过' ",
                "<if test='departmentId != null and departmentId != &apos;&apos;'>",
                    "AND t5.department_id = ${departmentId}",
                "</if>",
                "<if test='stationsId != null and stationsId != &apos;&apos;'>",
                    "AND t5.station_id IN ( ${stationsId} )",
                "</if>",
                "<if test='labourId != null and labourId != &apos;&apos;'>",
                    "AND t5.labour_id IN ( ${labourId} )",
                "</if>",
                "<if test='yearPart != null and yearPart != &apos;&apos;'>",
                    "AND t1.year_part = ${yearPart}",
                "</if>",
                "<if test='quarterNum != null and quarterNum != &apos;&apos;'>",
                    "AND t1.quarter_num = ${quarterNum}",
                "</if>",
                "<if test='startDate != null and startDate != &apos;&apos; and endDate != null and endDate != &apos;&apos;'>",
                    "AND t1.created_at BETWEEN '${startDate}' AND '${endDate}' ",
                "</if>",
                "<if test='subjectsId != null and subjectsId != &apos;&apos;'>",
                    "AND t5.subjects_id IN ( ${subjectsId} )",
                "</if>",
                "<if test='theirType != null and theirType != &apos;&apos;'>",
                    "AND t5.type = ${theirType}",
                "</if>",
            ") AS t01 ",
            "WHERE 1=1",
            "<if test='type != null and type != &apos;&apos;'>",
                "AND role_id = ${type}",
            "</if>",
            "GROUP BY",
            "grade_details_id",
            "</script>"})
    List<RecruitAnalysisRs> getTrialFormAnalysisList(@Param("yearPart") String yearPart,
                                                     @Param("quarterNum") String quarterNum,
                                                     @Param("startDate") String startDate,
                                                     @Param("endDate") String endDate,
                                                     @Param("stationsId") String stationsId,
                                                     @Param("departmentId") String departmentId,
                                                     @Param("subjectsId") String subjectsId,
                                                     @Param("labourId") String labourId,
                                                     @Param("type") String type,
                                                     @Param("theirType") String theirType
    );

    /**
     * 试用分析详情
     */
    @Select({"<script>",
            "SELECT * FROM (",
                "SELECT",
                "t1.id,",
                "t1.`name`,",
                "t3.id AS grade_details_id,",
                "t3.`name` AS grade_details_name,",
                "t4.grade,",
                "t4.option_text, ",
                "t5.name AS station_needs_name, ",
                "CASE WHEN EXISTS ( SELECT 1 FROM role_admin WHERE admin_id = t1.inviter_id AND role_id = 1568160915052400644 ) THEN 2 ELSE 1 END AS role_id ",
                "FROM",
                "adms_recruit AS t1",
                "INNER JOIN adms_estimate_details AS t2 ON t1.id = t2.recruit_id",
                "INNER JOIN adms_grade_details AS t3 ON t2.grade_details_id = t3.id ",
                "AND t3.deleted = 0",
                "INNER JOIN adms_grade_option AS t4 ON t2.option_id = t4.id ",
                "AND t4.deleted = 0",
                "INNER JOIN adms_try_job AS t6 ON t1.id = t6.recruit_id",
                "INNER JOIN adms_trial_form AS t7 ON t6.id = t7.try_job_id",
                "INNER JOIN adms_station_needs AS t5 ON t6.station_needs_id = t5.id ",
                "WHERE",
                "t7.pass_type = 2 ",
                "AND t2.type = 3 ",
                "AND t4.option_text = '不通过' ",
                "<if test='departmentId != null and departmentId != &apos;&apos;'>",
                    "AND t5.department_id = ${departmentId}",
                "</if>",
                "<if test='stationsId != null and stationsId != &apos;&apos;'>",
                    "AND t5.station_id IN ( ${stationsId} )",
                "</if>",
                "<if test='labourId != null and labourId != &apos;&apos;'>",
                    "AND t5.labour_id IN ( ${labourId} )",
                "</if>",
                "<if test='yearPart != null and yearPart != &apos;&apos;'>",
                    "AND t1.year_part = ${yearPart}",
                "</if>",
                "<if test='quarterNum != null and quarterNum != &apos;&apos;'>",
                    "AND t1.quarter_num = ${quarterNum}",
                "</if>",
                "<if test='startDate != null and startDate != &apos;&apos; and endDate != null and endDate != &apos;&apos;'>",
                    "AND t1.created_at BETWEEN '${startDate}' AND '${endDate}' ",
                "</if>",
                "<if test='subjectsId != null and subjectsId != &apos;&apos;'>",
                    "AND t5.subjects_id IN ( ${subjectsId} )",
                "</if>",
                "<if test='gradeDetailsId != null and gradeDetailsId != &apos;&apos;'>",
                    "AND t3.id = ${gradeDetailsId} ",
                "</if>",
                "<if test='theirType != null and theirType != &apos;&apos;'>",
                    "AND t5.type = ${theirType}",
                "</if>",
            ")AS t",
            "WHERE 1=1",
            "<if test='type != null and type != &apos;&apos;'>",
                "AND role_id = ${type}",
            "</if>",
            "</script>"})
    IPage<RecruitAnalysisRs> getTrialFormAnalysisDetails(IPage<RecruitAnalysisRs> page,
                                                         @Param("yearPart") String yearPart,
                                                         @Param("quarterNum") String quarterNum,
                                                         @Param("startDate") String startDate,
                                                         @Param("endDate") String endDate,
                                                         @Param("stationsId") String stationsId,
                                                         @Param("departmentId") String departmentId,
                                                         @Param("subjectsId") String subjectsId,
                                                         @Param("labourId") String labourId,
                                                         @Param("gradeDetailsId") String gradeDetailsId,
                                                         @Param("type") String type,
                                                         @Param("theirType") String theirType
    );

    @Select({"<script>",
            "WITH teacher_with AS (",
                    "SELECT id,NAME,IF( role_admin.admin_id IS NULL, 1, 2 ) AS is_person ",
                    "FROM",
                      "staff",
                      "LEFT JOIN role_admin ON staff.id = role_admin.admin_id ",
                      "AND role_admin.role_id = 1568160915052400644 ",
                    ") ",
                    "SELECT IFNULL( t2.is_person, 0 ) AS is_person ",
                    "FROM",
                    "adms_recruit AS t1",
                    "LEFT JOIN teacher_with AS t2 ON t1.inviter_id = t2.id ",
                    "WHERE",
                    "t1.id = ${recruitId}",
            "</script>"})
    public Integer getPersonType(@Param("recruitId") Long recruitId);

}
